#-*- encoding: utf-8 -*-
import win32com.client 
from Misc.Utils import *

from DataAccess.QueryBlp import *

from datetime import date,timedelta

from ReportTool.HldReport import *
from ReportTool.PnLReport import *
from ReportTool.NAVReport import *
from ReportTool.MktValReport import *
from ReportTool.TradeReport import *
from ReportTool.RealizedPnL import *
from ReportTool.OnshoreHldReport import *

from Config.ReportConfig import *	
	
def Newsletter(ref_date):

	nvpu_crt_us = query_latest_NAV(ref_date, ['SC028_US'])
	nvpu_crt_nonus = query_latest_NAV(ref_date, ['SC028_NONUS'])
	nvpu_icpt_nonus = query_inception_NAV(['SC028_NONUS'])
	nvpu_pre_1m_nonus = query_latest_NAV(end_of_mth(ref_date,-1), ['SC028_NONUS'])
	nvpu_pre_3m_nonus = query_latest_NAV(end_of_mth(ref_date,-3), ['SC028_NONUS'])
	nvpu_pre_6m_nonus = query_latest_NAV(end_of_mth(ref_date,-6), ['SC028_NONUS'])
	nvpu_pre_1y_nonus = query_latest_NAV(end_of_mth(ref_date,-12), ['SC028_NONUS'])
	nvpu_pre_3y_nonus = query_latest_NAV(end_of_mth(ref_date,-36), ['SC028_NONUS'])
	nvpu_pre_5y_nonus = query_latest_NAV(end_of_mth(ref_date,-60), ['SC028_NONUS'])
	nvpu_last_year_nonus = query_latest_NAV(end_of_mth(ref_date, -ref_date.month), ['SC028_NONUS'])
	hld = query_hld(ref_date, portf_list=['SC028'])
	av = query_latest_NAV(ref_date, ['SC028'], is_per_unit = False)
	
	file_list = ['Newsletter DATA', 'Newsletter DATA CH']
	xApp = win32com.client.Dispatch("Excel.Application")
	
	for file in file_list:
		tplt_file = TPLT_PATH + file + '.xls'
		xBook = xApp.Workbooks.Open(tplt_file)
		xSht = xBook.Worksheets('Data-Return')
		
		row = xSht.Range("A65536").End(win32com.client.constants.xlUp).Row + 1
		xSht.Cells(row, 1).Value = ref_date
		xSht.Cells(row, 2).Value = nvpu_crt_nonus[0][2]
		xSht.Cells(row, 3).Formula = '=B' + str(row) + '/B' + str(row-1) + '-1'
		xSht.Cells(row, 4).Formula = '=s_dq_close("000001.SH",A' + str(row) + ',1)/s_dq_close("000001.SH",$A$2,1)*100'
		xSht.Cells(row, 5).Formula = '=D' + str(row) + '/D' + str(row-1) + '-1'
		xSht.Cells(row, 6).Formula = '=s_dq_close("HSCEI.HI",A' + str(row) + ',1)/s_dq_close("HSCEI.HI",$A$2,1)*100'
		xSht.Cells(row, 7).Formula = '=F' + str(row) + '/F' + str(row-1) + '-1'
		xSht.Cells(row, 8).Formula = '=s_dq_close("HSI.HI",A' + str(row) + ',1)/s_dq_close("HSI.HI",$A$2,1)*100'
		xSht.Cells(row, 9).Formula = '=H' + str(row) + '/H' + str(row-1) + '-1'
		xSht.Cells(row, 10).Formula = '=J' + str(row-1) + '*(1+K' + str(row) + ')'
		xSht.Cells(row, 11).Value = 'Please input...'
		xSht.Cells(row, 12).Value = 'Please input...'
		xSht.Cells(row, 13).Formula = '=(1+C' + str(row) + ')^12-1-L' + str(row) + '/100'
		xSht.Cells(row, 14).Formula = '=(1+E' + str(row) + ')^12-1-L' + str(row) + '/100'
		xSht.Cells(row, 15).Formula = '=COVAR(M' + str(row-11) + ':M' + str(row) + ',N' + str(row-11) + ':N' + str(row) + ')/(STDEV(N' + str(row-11) + ':N' + str(row) + ')^2)'
		xSht.Cells(row, 16).Formula = '=AVERAGE(M' + str(row-11) + ':M' + str(row) + ')-O' + str(row) + '*AVERAGE(N' + str(row-11) + ':N' + str(row) + ')'
		xSht.Cells(row, 17).Formula = '=B' + str(row) + '/B' + str(row-12) + '-1'
		xSht.Cells(row, 18).Formula = '=D' + str(row) + '/D' + str(row-12) + '-1'
		xBook.Save()
		
		xSht.Cells(2, 22).Value = nvpu_crt_nonus[0][2]/nvpu_pre_1m_nonus[0][2] -1
		xSht.Cells(3, 22).Value = nvpu_crt_nonus[0][2]/nvpu_last_year_nonus[0][2] -1
		xSht.Cells(4, 22).Value = nvpu_crt_nonus[0][2]/nvpu_icpt_nonus[0][2]-1
		xSht.Cells(5, 22).Value = pow(nvpu_crt_nonus[0][2]/nvpu_icpt_nonus[0][2],365.0/(nvpu_crt_nonus[0][1]-nvpu_icpt_nonus[0][1]).days)-1
		xSht.Cells(6, 22).Formula = '=STDEV(C3:C' + str(row) + ')*SQRT(12)'
		xSht.Cells(7, 22).Formula = '=CORREL(C' + str(row-11) + ':C' + str(row) + ',E' + str(row-11) + ':E' + str(row) + ')'
		xSht.Cells(8, 22).Formula = '=CORREL(C' + str(row-11) + ':C' + str(row) + ',I' + str(row-11) + ':I' + str(row) + ')'
		
		region_expo ={}
		sector_expo ={}
		num_of_sec = 0
		amt = 0
		for elem in hld:
			if elem[2] == 'CASH':
				amt += elem[10]
				continue
			elif elem[2] == 'PNOTE':
				blp_ticker = ch_ticker_to_blp_ticker(elem[0])
			elif (elem[2] == 'STOCK' and elem[17] == 'HKD'):
				blp_ticker = hk_ticker_to_blp_ticker(elem[0])
			else:
				blp_ticker = elem[0]
			num_of_sec += 1
			region = sec_region(blp_ticker)
			sector = 'Others'
			if is_equity(blp_ticker):
				sector = query_blp_static([blp_ticker], ['GICS_INDUSTRY_NAME'])[blp_ticker]['GICS_INDUSTRY_NAME']
			
			if region_expo.has_key((region, elem[3])):
				region_expo[(region, elem[3])] += elem[12]
			else:
				region_expo.setdefault((region, elem[3]), elem[12])
			
			if sector_expo.has_key(sector):
				sector_expo[sector] += elem[12]
			else:
				sector_expo.setdefault(sector, elem[12])

		xSht = xBook.Worksheets('Data-Exposure')
		
		xSht.Cells(2, 2).Value = region_expo[('China A','LONG')] if region_expo.has_key(('China A','LONG')) else 0
		xSht.Cells(2, 3).Value = region_expo[('China A','SHORT')] if region_expo.has_key(('China A','SHORT')) else 0
		xSht.Cells(3, 2).Value = region_expo[('China B','LONG')] if region_expo.has_key(('China B','LONG')) else 0
		xSht.Cells(3, 3).Value = region_expo[('China B','SHORT')] if region_expo.has_key(('China B','SHORT')) else 0
		xSht.Cells(4, 2).Value = region_expo[('H Shares','LONG')] if region_expo.has_key(('H Shares','LONG')) else 0
		xSht.Cells(4, 3).Value = region_expo[('H Shares','SHORT')] if region_expo.has_key(('H Shares','SHORT')) else 0
		xSht.Cells(5, 2).Value = region_expo[('Other','LONG')] if region_expo.has_key(('Other','LONG')) else 0
		xSht.Cells(5, 3).Value = region_expo[('Other','SHORT')] if region_expo.has_key(('Other','SHORT')) else 0
		xSht.Cells(12, 2).Value = num_of_sec
		xSht.Cells(13, 2).Value = -amt/av[0][2] if amt<0 else 0
		
		row = 17
		for sector, expo in sorted(sector_expo.items(), key = lambda x:x[1], reverse = True):
			if sector != 'Others':
				xSht.Cells(row, 1).Value = sector
				xSht.Cells(row, 2).Value = expo
				row += 1
		
		xSht = xBook.Worksheets('Data-Performance')
		
		xSht.Range(xSht.Cells(2, 7), xSht.Cells(2, 8)).Value = nvpu_crt_nonus[0][1:]
		xSht.Range(xSht.Cells(3, 7), xSht.Cells(3, 8)).Value = nvpu_pre_1m_nonus[0][1:]
		xSht.Range(xSht.Cells(4, 7), xSht.Cells(4, 8)).Value = nvpu_pre_3m_nonus[0][1:]
		xSht.Range(xSht.Cells(5, 7), xSht.Cells(5, 8)).Value = nvpu_pre_6m_nonus[0][1:]
		xSht.Range(xSht.Cells(6, 7), xSht.Cells(6, 8)).Value = nvpu_pre_1y_nonus[0][1:]
		xSht.Range(xSht.Cells(7, 7), xSht.Cells(7, 8)).Value = nvpu_pre_3y_nonus[0][1:]
		xSht.Range(xSht.Cells(8, 7), xSht.Cells(8, 8)).Value = nvpu_pre_5y_nonus[0][1:]
		xSht.Range(xSht.Cells(9, 7), xSht.Cells(9, 8)).Value = nvpu_icpt_nonus[0][1:]
		for i in range(2, 10):
			xSht.Cells(i, 9).Formula = '=s_dq_close("000001.SH",G' + str(i) + ',1)'
			
		xSht = xBook.Worksheets('Newsletter')
		xSht.Cells(6, 1).Value = ref_date
		xSht.Cells(6, 3).Value = nvpu_crt_us[0][2]
		xSht.Cells(6, 4).Value = nvpu_crt_nonus[0][2]
		
		rept_file = REPT_PATH + file + '_' + ref_date.strftime("%Y%m%d") + '.xls'
		xBook.SaveAs(rept_file)
		xBook.Close()
		
		print 'info, ', file, ' completed at ', ref_date.isoformat()		
		
	del xApp